<?php
class PCalendarModellistview extends JModelLegacy
{
   public function getList($limitstart=-1, $limit=0) {
      $jobid = JRequest::getInt('jobid', 0);

      list($listorders) = PCalendarHelper::getListOrder();
      $nopagination = JRequest::getInt('nopagination', 0);

      $db = PFactory::getDbo();
      $searchSql = '';
      if ( $jobid > 0 ) {
         $searchSql .= " AND d.jobid = " . intval($jobid);
      }

      $app = PFactory::getApplication();
      $show_prev = $app->getUserState('calendar_showprev', 1);

      $today = JRequest::getInt('today',0);

      $selectquery = '';

      // $show_prev 0： 获取显示今天以后的   1：显示全部的 $today  1:显示当天的
      if(empty($show_prev)){
         list($starttime, $endtime) = PResumesHelper::getTimeRange();
         $todaystart = PFactory::getDate($starttime)->format('Y-m-d H:i:s');
         $selectquery = " AND t.startdate > ".$db->quote($todaystart);
      }

      $advancesearchlists = JRequest::get('get');
      $interviewfeedbacksql ='';
      //FIXME: need to fix for performance
      /* interviewfeedback:面试反馈   1:已完成 ,2：未完成  */
      if(!empty($advancesearchlists['interviewfeedback'])){
         foreach ($advancesearchlists['interviewfeedback'] as $seachlists){
            if(count($advancesearchlists['interviewfeedback'])>1){
               $interviewfeedbacksql .=" AND (SELECT COUNT(uid) FROM *PREFIX*calendar_interview_events  WHERE jobseekerid=cie.jobseekerid) = (SELECT COUNT(ofa2.id) FROM *PREFIX*calendar_feedback_answer AS ofa2 LEFT JOIN *PREFIX*calendar_interview_events AS cie2 ON ofa2.objectid=cie2.objectid  WHERE cie2.jobseekerid=cie.jobseekerid) 
                        AND (SELECT COUNT(uid) FROM *PREFIX*calendar_interview_events  WHERE jobseekerid=cie.jobseekerid)!=0
                        OR (SELECT COUNT(uid) FROM *PREFIX*calendar_interview_events  WHERE jobseekerid=cie.jobseekerid) > (SELECT COUNT(ofa2.id) FROM *PREFIX*calendar_feedback_answer AS ofa2 LEFT JOIN *PREFIX*calendar_interview_events AS cie2 ON ofa2.objectid=cie2.objectid  WHERE cie2.jobseekerid=cie.jobseekerid)
                        AND (SELECT COUNT(uid) FROM *PREFIX*calendar_interview_events  WHERE jobseekerid=cie.jobseekerid)!=0";
            }else{
               if ( $seachlists == 1 ) {
                  $interviewfeedbacksql .=" AND (SELECT COUNT(uid) FROM *PREFIX*calendar_interview_events  WHERE jobseekerid=cie.jobseekerid) = (SELECT COUNT(ofa2.id) FROM *PREFIX*calendar_feedback_answer AS ofa2 LEFT JOIN *PREFIX*calendar_interview_events AS cie2 ON ofa2.objectid=cie2.objectid  WHERE cie2.jobseekerid=cie.jobseekerid) 
                           AND (SELECT COUNT(uid) FROM *PREFIX*calendar_interview_events  WHERE jobseekerid=cie.jobseekerid)!=0 ";
               } else {
                  $interviewfeedbacksql .=" AND (SELECT COUNT(uid) FROM *PREFIX*calendar_interview_events  WHERE jobseekerid=cie.jobseekerid) > (SELECT COUNT(ofa2.id) FROM *PREFIX*calendar_feedback_answer AS ofa2 LEFT JOIN *PREFIX*calendar_interview_events AS cie2 ON ofa2.objectid=cie2.objectid  WHERE cie2.jobseekerid=cie.jobseekerid)
                           AND (SELECT COUNT(uid) FROM *PREFIX*calendar_interview_events  WHERE jobseekerid=cie.jobseekerid)!=0 ";
               }
            }
         }
      }
      //FIXME: need to fix for performance
      /* interviewresult:面试结果   1:录用 ,2：不录用 ,3：复试 ,4：缺席,5：已取消,6：正在面试 ,7:尚未开始,8:正在面试  */
      $interviewresult = '';
      $interviewresultsql ='';
      if(!empty($advancesearchlists['interviewresult']) && !empty($advancesearchlists['interviewresult2'])){
         foreach ($advancesearchlists['interviewresult'] as $seachlists){
            if($seachlists ==1 ||$seachlists ==2||$seachlists==3||$seachlists==4||$seachlists==5){
               $interviewresult .= $seachlists.',';
            }
         }
         $interviewresult = rtrim($interviewresult, ',');
         if(!empty($interviewresult)){
            $interviewresultsql .=" OR (SELECT result FROM *PREFIX*calendar_objects_details WHERE  objectid = (SELECT MAX(objectid) FROM *PREFIX*calendar_interview_events  WHERE eventtype='VEVENT' AND jobseekerid = cie.jobseekerid)) in (".$interviewresult.") ";
         }
         foreach ($advancesearchlists['interviewresult2'] as $seachlists){
            if($seachlists == 6){
               $interviewresultsql .=" OR '". PFactory::getDate() ."' > startdate ";
            }if($seachlists == 7){
               $interviewresultsql .=" OR (SELECT COUNT(uid) FROM *PREFIX*calendar_interview_events  WHERE jobseekerid=cie.jobseekerid) = 0 ";
            }if($seachlists == 8){
               $interviewresultsql .=" OR (SELECT COUNT(uid) FROM *PREFIX*calendar_interview_events  WHERE jobseekerid=cie.jobseekerid) > (SELECT COUNT(ofa2.id) FROM *PREFIX*calendar_feedback_answer AS ofa2 LEFT JOIN *PREFIX*calendar_interview_events AS cie2 ON ofa2.objectid=cie2.objectid  WHERE cie2.jobseekerid=cie.jobseekerid)
                                        AND (SELECT COUNT(uid) FROM *PREFIX*calendar_interview_events  WHERE jobseekerid=cie.jobseekerid)!=0 ";
            }
         }
      }

      if(!empty($advancesearchlists['interviewresult']) && empty($advancesearchlists['interviewresult2'])){
         foreach ($advancesearchlists['interviewresult'] as $seachlists){
            if($seachlists ==1 ||$seachlists ==2||$seachlists==3||$seachlists==4||$seachlists==5){
               $interviewresult .= $seachlists.',';
            }
         }
         $interviewresult = rtrim($interviewresult, ',');
         if(!empty($interviewresult)){
            $interviewresultsql .=" AND (SELECT result FROM *PREFIX*calendar_objects_details WHERE  objectid = (SELECT MAX(objectid) FROM *PREFIX*calendar_interview_events  WHERE eventtype='VEVENT' AND jobseekerid = cie.jobseekerid)) in (".$interviewresult.") ";
         }
      }

      if(empty($advancesearchlists['interviewresult']) && !empty($advancesearchlists['interviewresult2'])){
         foreach ($advancesearchlists['interviewresult2'] as $seachlists){
            if ($seachlists == 6) {
               $interviewresultsql .=" OR '". PFactory::getDate() ."' > startdate ";
            }
            if ($seachlists == 7) {
               $interviewresultsql .=" OR (SELECT COUNT(uid) FROM *PREFIX*calendar_interview_events  WHERE jobseekerid=cie.jobseekerid) = 0 ";
            }
            if ($seachlists == 8) {
               $interviewresultsql .=" OR (SELECT COUNT(uid) FROM *PREFIX*calendar_interview_events  WHERE jobseekerid=cie.jobseekerid) > (SELECT COUNT(ofa2.id) FROM *PREFIX*calendar_feedback_answer AS ofa2 LEFT JOIN *PREFIX*calendar_interview_events AS cie2 ON ofa2.objectid=cie2.objectid  WHERE cie2.jobseekerid=cie.jobseekerid)
                  AND (SELECT COUNT(uid) FROM *PREFIX*calendar_interview_events  WHERE jobseekerid=cie.jobseekerid)!=0 ";
            }
         }
      }

      //FIXME: need to fix for performance
      /* offer:面试结果   1:接受offer ,2:拒绝offer ,3:未发 ,0:已发  */
      if(!empty($advancesearchlists['offer'])){
         foreach ($advancesearchlists['offer'] as $seachlists){
            if ($seachlists == 0) {
               $interviewresultsql .=" OR (SELECT COUNT(*) FROM *PREFIX*calendar_offer WHERE jobseekerid = cie.jobseekerid)>0 ";
            }
            if ($seachlists == 3) {
               $interviewresultsql .=" OR (SELECT COUNT(*) FROM *PREFIX*calendar_offer WHERE jobseekerid = cie.jobseekerid)<0 ";
            }
            if ($seachlists == 1) {
               $interviewresultsql .=" OR (SELECT offer_feedback FROM *PREFIX*calendar_offer co LEFT JOIN *PREFIX*calendar_objects_details  od ON co.objectid = od.objectid LEFT JOIN *PREFIX*calendar_offer_feedback cofb ON od.offer_stat = cofb.id WHERE co.jobseekerid = cie.jobseekerid) = '接受offer' ";
            }
            if ($seachlists == 2) {
               $interviewresultsql .=" OR (SELECT offer_feedback FROM *PREFIX*calendar_offer co LEFT JOIN *PREFIX*calendar_objects_details  od ON co.objectid = od.objectid LEFT JOIN *PREFIX*calendar_offer_feedback cofb ON od.offer_stat = cofb.id WHERE co.jobseekerid = cie.jobseekerid) = '拒绝offer'  ";
            }
         }
      }
      //FIXME: need to fix for performance
      /*  entry:入职  1:已入职 ,0:已过期  */
      if(!empty($advancesearchlists['entry'])){
         foreach ($advancesearchlists['entry'] as $seachlists){
            if($seachlists == 1){
               $interviewresultsql .=" OR (SELECT offer_feedback FROM *PREFIX*calendar_offer co LEFT JOIN *PREFIX*calendar_objects_details  od ON co.objectid = od.objectid LEFT JOIN *PREFIX*calendar_offer_feedback cofb ON od.offer_stat = cofb.id WHERE co.jobseekerid = cie.jobseekerid) = '已入职' ";
            }if($seachlists == 0){
               $interviewresultsql .=" OR (SELECT entry_deadline FROM *PREFIX*calendar_offer WHERE jobseekerid = cie.jobseekerid) < '". PFactory::getDate() ."' ";
            }
         }
      }

      $keyword = JRequest::getString("keyword", "");
      $keywordsql = "";
      if($keyword) {
         $keywordsql = " AND ( js.name LIKE " . $db->quote("%".$keyword."%") ." OR js.phone LIKE " . $db->quote("%".$keyword."%") ." OR js.email LIKE " . $db->quote("%".$keyword."%") ." ) ";
      }

      $master_uid =  PFactory::getMasterUser()->id;
      $userid = OC_USER::getUser();

      // The logical is similar to function in object.php/allInPeriod, except that this query gets only main interview object,
      // thus, the object may not be in the userid calendar account(sharing event case), so we have to check uid on interview_events 
      // table.
      $query ="SELECT SQL_CALC_FOUND_ROWS v.jobseekername,jj.job_title,d.jobseekerid,d.jobid,d.last_interview_objectid AS objectid,count(o.id) AS offerstat,cr.result,cof.offer_feedback AS offerresult,e.round,t.startdate,t.enddate,cef.entry_feedback AS entryresult,
               d.totalinterviewers AS totalinterviewers,
               d.totalfeedbacks AS totalfeedbacks,
               t.summary,cc.id as calendarid,
               o.entry_deadline,
               o.objectid AS offereventid,".
               ( PFactory::isMasterUser() ? " 1 as haspermission, " : " IF(jm.jobid IS NULL, 0, 1) as haspermission, " ) .
               "e.interview_result,
               d.canceled,js.email
               FROM *PREFIX*calendar_interview_result AS d
               LEFT JOIN *PREFIX*calendar_objects_details AS e ON e.objectid=d.last_interview_objectid
               LEFT JOIN *PREFIX*calendar_interview_events AS v ON v.jobseekerid=d.jobseekerid
               LEFT JOIN *PREFIX*calendar_objects AS t ON t.id=d.last_interview_objectid
               LEFT JOIN *PREFIX*calendar_objects AS os ON os.id=v.objectid
               LEFT JOIN *PREFIX*calendar_interview_events AS ee ON ee.parent = os.id
               LEFT JOIN *PREFIX*calendar_offer AS o on o.objectid=d.last_offer_objectid
               LEFT JOIN #__jobs_job AS jj ON jj.id=d.jobid
               LEFT JOIN *PREFIX*calendar_result AS cr ON cr.id=e.result
               LEFT JOIN *PREFIX*calendar_offer_feedback cof ON cof.id=e.offer_stat
               LEFT JOIN *PREFIX*calendar_entry_feedback cef ON cef.id=e.entry_stat
               LEFT JOIN *PREFIX*calendar_calendars AS cc ON cc.id=os.calendarid 
               LEFT JOIN *PREFIX*hr_jobseeker AS js ON js.id=v.jobseekerid " .
               ( PFactory::isMasterUser() ? " LEFT JOIN *PREFIX*users_extended ux ON ux.uid = cc.userid " : "" ) .
               ( PFactory::isMasterUser() ? "" : " LEFT JOIN *PREFIX*hr_jobmanager jm ON (jm.jobid=e.jobid AND jm.manager=".$db->quote($userid).")" ) .
               " WHERE jj.employer_id = ".intval($master_uid)." AND " .
               ( PFactory::isMasterUser() ? " ( cc.userid = ".intval($userid)." OR ee.uid=".intval($userid) ." OR ux.owner = ".intval($userid)." )" : " ( cc.userid = ".$db->quote($userid)." OR ee.uid=".$db->quote($userid) . " OR jm.manager = ".$db->quote($userid).")" ) .$keywordsql;

      $query .= $searchSql." ".$interviewfeedbacksql."  ".$interviewresultsql." ".$selectquery." ";
      $query .=" GROUP BY d.jobseekerid";
      $query .=" ORDER BY ". implode(", ", $listorders);

      $limitstart = $limitstart >= 0  ? $limitstart : JRequest::getInt('limitstart', 0);
      // Get the pagination request variables
      $app = PFactory::getApplication();
      $limit = $limit > 0 ? $limit : $app->getUserStateFromRequest('calendarlists.limit', 'limit', 20, 'int');
      $limit = $limit > 100 ? 100 : $limit;

      // In case limit has been changed, adjust limitstart accordingly
      $limitstart = ( $limit != 0 ? (floor( $limitstart / $limit ) * $limit) : 0);

      // Try to load from cache
      $cache = CacheHelper::getCache('app_calendar');
      // Get cached data now
      $result = $cache->call(array($this, '_getList'), $query, $limitstart, $limit);
      // Event Driven Cache/Object Caching
      CacheHelper::setTriggers($master_uid, $cache, array('*PREFIX*calendar_interview_events', '*PREFIX*calendar_feedback_answer', '*PREFIX*calendar_objects_details', '*PREFIX*calendar_offer', '*PREFIX*calendar_offer_feedback', '*PREFIX*calendar_interview_result', '*PREFIX*calendar_objects', '#__jobs_job', '*PREFIX*calendar_result', '*PREFIX*calendar_calendars', '*PREFIX*users_extended', '*PREFIX*hr_jobmanager'));

      $total = 0;
      if(!$nopagination) {
         $total = $result['total'];
      }

      $pagination = new PPagination($total, $limitstart, $limit);
      return array($result['list'], $pagination);
   }

   public function _getList($query, $limitstart = 0, $limit = 0) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query), $limitstart, $limit);
      $result = $db->loadObjectList();
      $db->setQuery($db->useLastConnection('SELECT FOUND_ROWS();'));
      $total = $db->loadResult();
      return array('list' => $result, 'total' => $total);
   }

   public function getUserInterviewList($jobseeker_id) {
      $db = PFactory::getDbo();
      $userid = OC_User::getUser();
      $master_uid = PFactory::getMasterUser()->get("id");
      $query = "SELECT DISTINCT cie.parent,cod.round,cod.result, cie.jobseekername, jj.job_title, jj.id AS jobid, co.startdate, co.enddate, cir.last_offer_objectid, cir.last_interview_objectid, cod.entry_stat, cod.totalfeedbacks AS last_round_totalfeedbacks
         FROM *PREFIX*calendar_interview_events cie
         LEFT JOIN *PREFIX*calendar_objects_details cod ON cie.parent = cod.objectid
         LEFT JOIN *PREFIX*calendar_objects co ON cie.parent = co.id
         LEFT JOIN *PREFIX*calendar_calendars cc ON co.calendarid = cc.id
         LEFT JOIN #__jobs_job jj ON cod.jobid=jj.id
         LEFT JOIN *PREFIX*calendar_interview_result cir ON cir.jobseekerid= cie.jobseekerid
         WHERE jj.employer_id = ".intval($master_uid)." AND 
         cie.jobseekerid=".intval($jobseeker_id)." AND cie.eventtype='VEVENT' ORDER BY cie.parent DESC";

      // Try to load from cache
      $cache = CacheHelper::getCache('app_calendar');
      // Get cached data now
      $result = $cache->call(array($this, '_getUserInterviewList'), $query);
      // Event Driven Cache/Object Caching
      if ($result) {
         CacheHelper::setTriggers($master_uid, $cache, array('*PREFIX*calendar_interview_events-'.$result['0']['parent'], '*PREFIX*calendar_objects_details-'.$result['0']['parent'],'*PREFIX*calendar_objects-'.$result['0']['parent'],'*PREFIX*calendar_calendars','#__jobs_job','*PREFIX*users_extended','*PREFIX*hr_jobmanager','*PREFIX*calendar_interview_result'));
      } else {
         // Don't cache null data
         $cache->removeCache(array($this, '_getUserInterviewList'), $query);
      }

      return $result;
   }

   public function _getUserInterviewList($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList();
   }

   public function getInterviewRound($jobseeker_id) {
      if(empty($jobseeker_id))
         return null;
      $db = PFactory::getDbo();
      $master_uid = PFactory::getMasterUser()->get("id");
      $query = "SELECT SQL_CALC_FOUND_ROWS cie.parent, cie.next, cie.jobseekername, jj.job_title, co.startdate, cod.round, cod.jobid, cie.objectid
         FROM *PREFIX*calendar_interview_events AS cie
         LEFT JOIN *PREFIX*calendar_objects AS co ON cie.objectid = co.id
         LEFT JOIN *PREFIX*calendar_objects_details AS cod ON cie.objectid = cod.objectid
         LEFT JOIN  #__jobs_job AS jj ON jj.id = cod.jobid
         WHERE jj.employer_id = ".intval($master_uid)." AND cie.jobseekerid=".intval($jobseeker_id)." AND co.objecttype='VEVENT' GROUP BY cie.parent ORDER BY cie.parent DESC";
      // Try to load from cache
      $cache = CacheHelper::getCache('app_calendar');
      // Get cached data now
      $result = $cache->call(array($this, '_getInterviewRound'), $query);
      // Event Driven Cache/Object Caching
      if ($result) {
         CacheHelper::setTriggers(PFactory::getMasterUser()->get('id'), $cache, array('*PREFIX*calendar_interview_events-'.$result[0][0]['parent'], '*PREFIX*calendar_objects-'.$result[0][0]['parent'],'*PREFIX*calendar_objects_details-'.$result[0][0]['parent'],'#__jobs_job'));
      } else {
         // Don't cache null data
         $cache->removeCache(array($this, '_getInterviewRound'), $query);
      }

      return $result;
   }

   public function _getInterviewRound($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $round = $db->loadAssocList();
      $db->setQuery($db->useLastConnection('SELECT FOUND_ROWS();'));
      $total = $db->loadResult();
      return array($round, $total);
   }

   public function getInterviewFeedbackList($jobseeker_id) {
      if(empty($jobseeker_id))
         return null;
      $userid = OC_USER::getUser();
      $master_uid = PFactory::getMasterUser()->get("id");

      $db = PFactory::getDbo();
      $query ="SELECT ee.objectid, ee.absent_flag, group_concat(IF(ee.displayname IS NOT NULL AND length(ee.displayname)>0, ee.displayname, ee.uid)) AS interviewers, cfa.checkpoints, cfa.fb_time, cfs.suggestion, cc.userid, ie.jobid, cod.round, cod.result, ie.parent, s.token, ee.uid AS user_email, cfa.suggestion_id
         FROM *PREFIX*calendar_interview_events AS ee
         LEFT JOIN *PREFIX*calendar_feedback_answer AS cfa ON cfa.objectid = ee.objectid
         LEFT JOIN *PREFIX*calendar_objects_details AS cod ON ee.parent = cod.objectid
         LEFT JOIN *PREFIX*calendar_feedback_suggestion AS cfs ON cfa.suggestion_id = cfs.id
         LEFT JOIN *PREFIX*share s ON s.share_with = ee.uid AND s.item_source = ee.objectid
         LEFT JOIN *PREFIX*calendar_interview_events AS ie ON ie.objectid = ee.parent
         LEFT JOIN *PREFIX*calendar_objects AS co ON co.id = ie.objectid
         LEFT JOIN *PREFIX*calendar_calendars AS cc ON cc.id = co.calendarid
         LEFT JOIN #__jobs_job jj ON jj.id = ie.jobid ".
         " WHERE jj.employer_id = ".intval($master_uid)." AND ee.objectid <> ee.parent AND ee.jobseekerid = ".intval($jobseeker_id)." AND ee.eventtype = 'VEVENT'".
         " GROUP BY ee.objectid ORDER BY round ASC";

      // Try to load from cache
      $cache = CacheHelper::getCache('app_calendar');
      // Get cached data now
      $result = $cache->call(array($this, '_getInterviewFeedbackList'), $query);
      // Event Driven Cache/Object Caching
      if ($result) {
         if(CacheHelper::needTriggers($cache)) {
            foreach($result as $r) {
               CacheHelper::setTriggers($master_uid, $cache, array('*PREFIX*calendar_interview_events-'.$r['parent'], '*PREFIX*calendar_feedback_answer-'.$r['parent'], '*PREFIX*calendar_objects_details-'.$r['parent'], '*PREFIX*calendar_objects-'.$r['parent'])); 
            }

            CacheHelper::setTriggers($master_uid, $cache, array('*PREFIX*calendar_calendars','#__jobs_job','*PREFIX*share'));
         }
      } else {
         // Don't cache null data
         $cache->removeCache(array($this, '_getInterviewFeedbackList'), $query);
      }

      return $result;
   }

   public function _getInterviewFeedbackList($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList();
   }

   public function setInterviewResult() {
      if(!PSession::checkToken('post')) {
         return false;
      }

      $resultid = JRequest::getInt('resultid', 0);
      $objectid = JRequest::getInt('objectid', 0);
      $jobseekerid = JRequest::getInt('jobseekerid', 0);
      $statusid = JRequest::getInt('statusid', 0);
      $jobid = JRequest::getInt('jobid', 0);

      if(empty($jobid))
         return false;

      if ( !PCompanyHelper::checkJobManagerPermissions($jobid) )
         return false;

      if ( empty($resultid) || empty($objectid) || empty($jobseekerid) || empty($statusid) )
         return false;

      $db = PFactory::getDbo();
      // Update interview result in oc_calendar_objects_details table
      $query ="UPDATE *PREFIX*calendar_objects_details SET result=".intval($resultid)." WHERE objectid= ".intval($objectid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if(!$db->execute()) {
         return false;
      }
      // Event Driven Cache/Object Caching - clear any cache related to affected tables
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get('id'), array('*PREFIX*calendar_objects_details-'.$objectid));
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get('id'), array('*PREFIX*calendar_objects_details'));

      //check we have send or have not send the cancelemail
      $query = "SELECT canceled,rejected FROM *PREFIX*calendar_interview_result WHERE last_interview_objectid= ".intval($objectid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $cancelemailstatus = $db->loadAssoc();

      if($resultid == PCalendarHelper::INTERVIEW_RESULT_CANCEL){
         if(!self::sendCancelEmail($jobseekerid, $objectid)){
            return false;
         }
      }

      if ( !PResumesHelper::setResumeStatus($jobseekerid,$statusid) )
         return false;

      return true;
   }

   public function setOfferResult() {
      if(!PSession::checkToken('post')) {
         return false;
      }

      $jobseekerid = JRequest::getInt('jobseekerid', 0);
      $offerstat = JRequest::getInt('offerstat', 0);
      $statusid = JRequest::getInt('statusid', 0);
      $objectid = JRequest::getInt('objectid', 0);
      $jobid = JRequest::getInt('jobid', 0);

      if(empty($jobseekerid) || empty($offerstat) || empty($statusid) || empty($jobid))
         return false;

      if(!PCompanyHelper::checkJobManagerPermissions($jobid))
         return false;

      $db = PFactory::getDbo();
      $query = "SELECT last_offer_objectid FROM *PREFIX*calendar_interview_result WHERE jobseekerid = ".intval($jobseekerid);
      $db->setQuery(PUtil::replaceOCPrefix($db->useMaster($query)));
      $offereventid = $db->loadResult();

      if(empty($offereventid))
         return false;

      $query ="UPDATE *PREFIX*calendar_objects_details SET offer_stat=".intval($offerstat)." WHERE objectid in (".intval($objectid).",".intval($offereventid).")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if(!$db->execute()) {
         return false;
      }
      // 接收offer，未选择入职状态，默认入职状态应该是待入职
      if($offerstat == PCalendarHelper::OFFER_FEEDBACKSTATUS_ACCEPTED){
         $this->setEntryResult(PCalendarHelper::ENTRY_FEEDBACKSTATUS_NOENTRY);
      }
      // Event Driven Cache/Object Caching - clear any cache related to affected tables
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects_details-'.$objectid));
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects_details'));

      if (!PResumesHelper::setResumeStatus($jobseekerid,$statusid)) {
         return false;
      }

      return true;
   }

   public function getJobseekerInfo($jobseekerid){
      if(empty($jobseekerid))
         return null;
      $db = PFactory::getDbo();
      $query = "SELECT a.email,a.source,a.name as jobseekername,b.source as jobsource FROM *PREFIX*hr_jobseeker as a
                LEFT JOIN #__jobs_job as b ON b.id = a.job_id
                WHERE a.id = ".intval($jobseekerid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssoc();
   }

   public function getFeedbackQuestion(){
      $db = PFactory::getDbo();
      $query = "SELECT id,question FROM *PREFIX*calendar_feedback_question order by id ASC";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList();
   }

   public function getFeedbackSuggestion(){
      $db = PFactory::getDbo();
      $query = "SELECT id,suggestion FROM *PREFIX*calendar_feedback_suggestion order by id ASC";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList();
   }

   public function getFeedbackNum($objectid){
      $db = PFactory::getDbo();
      $query = "SELECT * FROM *PREFIX*calendar_feedback_answer where objectid = ".$objectid;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList();
   }

   public function setInterviewFeedbackByObjectid($companyid) {
      if(!PSession::checkToken('post')) {
         return false;
      }

      $objectid = JRequest::getInt('objectid', 0);
      $uid = JRequest::getString('uid', '');
      if(empty($uid)){
         $this->setError('请您填写面试官邮箱地址!');
         return false;
      }
      $displayname = JRequest::getString("displayname", '');
      if(strlen(trim($displayname)) <= 0) {
         $this->setError('请您填写面试官姓名!');
         return false;
      }
      //$displayname = PCalendarHelper::getDisplayName($uid);
      $round = JRequest::getInt('round', 0);
      if(empty($round)){
         $this->setError('网络错误 请重试!');
         return false;
      }
      $jobseekerid = JRequest::getInt('jobseekerid', 0);
      if(empty($jobseekerid)){
         $this->setError('网络错误 请重试!');
         return false;
      }

      $db = PFactory::getDbo();

      $absent = JRequest::getInt("absent", 0);
      $absent = $absent > 0 ? 1 : 0;

      $notattended = JRequest::getInt("notattended", 0);
      $notattended = $notattended > 0 ? 1 : 0;

      $comment = JRequest::getString('comment', '');
      if(empty($comment) && !$absent && !$notattended) {
         $this->setError('请您给评语!');
         return false;
      }

      $suggestion = JRequest::getInt('suggestion', 0);
      if(empty($suggestion) && !$absent && !$notattended) {
         $this->setError('请您做出建议!');
         return false;
      }

      $feedbacknum = $this->getFeedbackNum($objectid);
      if(count($feedbacknum)>=1){
         $this->setError('您已经做过反馈!');
         return false;
      }
      
      //6代表面试官未参加面试
      if(!empty($notattended)) {
         $suggestion = 6;
      }
      /*1:性格 2：团队精神 3：工作态度及积极性  4:专业技能 5:工作经验 6:教育背景 7:交流沟通能力 8:学习能力 9：创新能力 10:交际处事能力 11：领导管理才能*/
      $questionlists = array(
         '1'=>JRequest::getInt('1', 0),
         '2'=>JRequest::getInt('2', 0),
         '3'=>JRequest::getInt('3', 0),
         '4'=>JRequest::getInt('4', 0),
         '5'=>JRequest::getInt('5', 0),
         '6'=>JRequest::getInt('6', 0),
         '7'=>JRequest::getInt('7', 0),
         '8'=>JRequest::getInt('8', 0),
         '9'=>JRequest::getInt('9', 0),
         '10'=>JRequest::getInt('10', 0),
         '11'=>JRequest::getInt('11', 0)
      );

      $questionlists = json_encode($questionlists);

      $query ="INSERT INTO *PREFIX*calendar_feedback_answer (objectid,userid,displayname,checkpoints,suggestion_id,comment,fb_time,round) VALUES (".intval($objectid).",".$db->quote($uid).",".$db->quote($displayname).",".$db->quote($questionlists).",".intval($suggestion).",".$db->quote($comment).",UTC_TIMESTAMP(),".intval($round).")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if(!$db->execute()) {
         $this->setError('保存失败');
         return false;
      }

      $query ="UPDATE *PREFIX*calendar_objects_details SET totalfeedbacks =  totalfeedbacks+1 WHERE  objectid = (SELECT parent FROM *PREFIX*calendar_interview_events WHERE objectid = ".intval($objectid).")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if(!$db->execute()) {
         $this->setError('保存失败');
         return false;
      }

      $query ="UPDATE *PREFIX*calendar_interview_result SET totalfeedbacks =  totalfeedbacks+1 WHERE  jobseekerid = ".intval($jobseekerid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if(!$db->execute()) {
         $this->setError('保存失败');
         return false;
      }

      // Event Driven Cache/Object Caching - clear any cache related to affected tables
      $query = "SELECT parent FROM *PREFIX*calendar_interview_events WHERE objectid = ".intval($objectid)." LIMIT 1";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $parent_id = $db->loadResult();

      CacheHelper::triggerRemove($companyid, array('*PREFIX*calendar_feedback_answer-'.$parent_id, '*PREFIX*calendar_objects_details-'.$parent_id, '*PREFIX*calendar_interview_result-'.$parent_id));
      CacheHelper::triggerRemove($companyid, array('*PREFIX*calendar_feedback_answer', '*PREFIX*calendar_objects_details', '*PREFIX*calendar_interview_result'));

      return true;
   }

   public function setSendDeclineStatusByObjectid($objectid){
      //set interview_result = 1 :"已经发过不录用通知"
      $db = PFactory::getDbo();
      $query ="UPDATE *PREFIX*calendar_objects_details SET interview_result = 1 WHERE objectid  = ".intval($objectid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if(!$db->execute()) {
         return false;
      }
      // Event Driven Cache/Object Caching - clear any cache related to affected tables
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects_details-'.$objectid));
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects_details'));
      return true;
   }

   public function setEntryResult($entrystat=0) {
      if(!PSession::checkToken('post')) {
         return false;
      }

      $jobseekerid = JRequest::getInt('jobseekerid', 0);
      if(empty($entrystat)){
         $entrystat = JRequest::getInt('entrystat', 0);
      }   
      $objectid = JRequest::getInt('objectid', 0);
      $jobid = JRequest::getInt('jobid', 0);
      $statusid = JRequest::getInt('statusid', 0);

      if(empty($jobseekerid) || empty($entrystat) || empty($objectid) || empty($jobid) || empty($statusid))
         return false;

      if(!PCompanyHelper::checkJobManagerPermissions($jobid))
         return false;

      $db = PFactory::getDbo();
      $query ="UPDATE *PREFIX*calendar_objects_details SET entry_stat=".intval($entrystat)." WHERE objectid =".intval($objectid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if(!$db->execute()) {
         return false;
      }
      // Event Driven Cache/Object Caching - clear any cache related to affected tables
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects_details-'.$objectid));
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects_details'));

      if(!PResumesHelper::setResumeStatus($jobseekerid,$statusid)){
         return false;
      }

      return true;
   }

   /**
    * Send jobseeker decline email
    * @param  string  $jobseekeremail Jobseeker's email
    * @param  array   $round          All round's info
    * @param  integer $total          Round count
    * @param  array   $user           Company user info
    * @param  string  $jobseekername  Jobseeker's name
    * @return true
    */
   public function sendDeclineEmail($jobseekerinfo, $round, $total, $user) {
      jimport('joomla.mail.helper');
      if ( JMailHelper::isEmailAddress($jobseekerinfo['email']) && !isFakeEmail($jobseekerinfo['email'])) {
         require_once JPATH_SITE.'/components/com_jobmail/models/email.php';
         $mailer = new JobmailModelEmail();
         $config = PFactory::getConfig();
         $receiver = (isDevMode() || isTestMode() || isTestUser($user['email'])) ? $config->get('jobseekeremail', '') : $jobseekerinfo['email'];
         $supportmail = $config->get('mailfrom','');
         $body = '<p class="p_text t_intent">非常感谢您来我公司参加 <b>'.$round[$total-1]['job_title'].'</b> 职位面试，您的技能和资历给我们留下了深刻印象。但是经过认真考虑，我们觉得该职位的要求跟您的背景不完全匹配。您的资料已经纳入到我公司人才库，有更适合的新职位时，您会收到申请的邀请。衷心希望您浏览我公司其他职位，并继续关注我公司的发展和招聘需求。</p>';

         $mailer->setTemplate('interview_result');
         $mailer->setVariables(array(
            'recipient'=>$receiver,
            'source'=>$jobseekerinfo['source'],
            'jobsource'=>$jobseekerinfo['jobsource'],
            'fromName'=>'枇杷派云招聘',
            'subject'=>$user['comp_name'].' '.$round[$total-1]['job_title'].'面试结果通知',
            'jobseeker'=>$jobseekerinfo['jobseekername'],
            'companyname'=>$user['comp_name'],
            'jobtitle'=>$round[$total-1]['job_title'],
            'supportmail'=>$supportmail,
            'hrurl'=>'http://'.OC_Request::serverHost(),
            'body'=>$body
            )
         );

         $mailer->send();
      }
   }

   public function getTodayEventlist($limitstart=-1, $limit=0){
      $master_uid =  PFactory::getMasterUser()->id;
      $userid = OC_USER::getUser();

      list($starttime, $endtime) = PResumesHelper::getTimeRange();
      $todaystart = PFactory::getDate($starttime)->format('Y-m-d H:i:s');
      $todayend = PFactory::getDate($starttime+24*3600)->format('Y-m-d H:i:s');

      $db = PFactory::getDbo();
      $query = " SELECT SQL_CALC_FOUND_ROWS * FROM (
                 SELECT DISTINCT b.*, ie.parent, ie.uid,ie.jobseekerid,ie.jobseekername
                 FROM *PREFIX*calendar_objects b
                 LEFT JOIN *PREFIX*calendar_calendars c ON c.id = b.calendarid
                 LEFT JOIN *PREFIX*calendar_interview_events ie ON ie.objectid = b.id
                 LEFT JOIN *PREFIX*calendar_objects_details od ON od.objectid = ie.parent " .
                 ( PFactory::isMasterUser() ? " LEFT JOIN *PREFIX*users_extended ux ON ux.uid = c.userid " : "" ) .
                 ( PFactory::isMasterUser() ? "" : " LEFT JOIN *PREFIX*hr_jobmanager jm ON jm.jobid = od.jobid" ) .
                 ( PFactory::isMasterUser() ? " WHERE ie.parent = od.objectid  AND IF(b.objecttype='VEVENT',od.result<>5,od.offer_stat<>2) AND " : " WHERE IF(b.objecttype='VEVENT',od.result<>5,od.offer_stat<>2) AND " ) .
                 ( PFactory::isMasterUser() ? " ( c.userid = ".intval($userid)." OR ( ux.deleted=0 AND ux.owner = ".intval($userid).") )" : " ( c.userid = ".$db->quote($userid)." OR jm.manager = ".$db->quote($userid).")" ) .
               " ORDER BY b.startdate ASC ) AS r  WHERE startdate >= ".$db->quote($todaystart)." AND startdate <= ".$db->quote($todayend)." ".
                 ( PFactory::isMasterUser() ? " HAVING r.parent=r.id " : " GROUP BY r.parent HAVING r.parent=r.id OR (r.uid = ".$db->quote($userid)." AND r.parent <> r.id ) ORDER BY startdate ASC");

      $limitstart = $limitstart >= 0  ? $limitstart : JRequest::getInt('limitstart', 0);
      // Get the pagination request variables
      $app = PFactory::getApplication();
      $limit = $limit > 0 ? $limit : $app->getUserStateFromRequest('calendartodaylist.limit', 'limit', 20, 'int');
      $limit = $limit > 100 ? 100 : $limit;

      // In case limit has been changed, adjust limitstart accordingly
      $limitstart = ( $limit != 0 ? (floor( $limitstart / $limit ) * $limit) : 0);

      // Try to load from cache
      $cache = CacheHelper::getCache('app_calendar');
      // Get cached data now
      $result = $cache->call(array($this, '_getTodayEventlist'), $query, $limitstart, $limit);
      // Event Driven Cache/Object Caching
      CacheHelper::setTriggers($master_uid, $cache, array('*PREFIX*calendar_interview_events', '*PREFIX*calendar_feedback_answer', '*PREFIX*calendar_objects_details', '*PREFIX*calendar_offer', '*PREFIX*calendar_offer_feedback', '*PREFIX*calendar_interview_result', '*PREFIX*calendar_objects', '#__jobs_job', '*PREFIX*calendar_result', '*PREFIX*calendar_calendars', '*PREFIX*users_extended', '*PREFIX*hr_jobmanager'));

      $nopagination = JRequest::getInt('nopagination', 0);
      $total = 0;
      if(!$nopagination) {
      	$total = $result['total'];
      }

      $pagination = new PPagination($total, $limitstart, $limit);
      return array($result['list'], $pagination);
   }

   public function _getTodayEventlist($query, $limitstart = 0, $limit = 0) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query), $limitstart, $limit);
      $result = $db->loadObjectList();
      $db->setQuery($db->useLastConnection('SELECT FOUND_ROWS();'));
      $total = $db->loadResult();
      return array('list' => $result, 'total' => $total);
   }

   public function uploadAttachment($type = 'offer'){
      $upload =array(
         'tmp_name' => null,
         'name' => null,
         'size' => null,
         'type' => null,
         'error' => ""
      );
      if (!PSession::checkToken('post')) {
         $upload['error'] = '页面已失效，请刷新页面！';
         return json_encode($upload);
      }
      
      $tag = ($type == 'interview')?2:1;
      $files = JRequest::get('files');
      $user_id = PFactory::getMasterUser()->id;
     
      if(!empty($files['file']['name'])) {
         $offer_data = $files['file'];
         $datadir = getUserDataDir($user_id);
         $extdir = getUserExtDir($user_id);
         $target_path = OC::$SERVERROOT."/data".$datadir."/company/" . $extdir . "/files/".$type."/";

         if(!JFolder::exists($target_path))
            JFolder::create($target_path);

         $filename = $offer_data['name'];

         try { 
            $charset = detectCharsetEncoding(null, $offer_data['name']);
            if($charset != false && $charset != 'UTF-8') {
               $filename = mb_convert_encoding($offer_data['name'] , 'UTF-8' ,  $charset);
            }
         } catch(Exception $e) {}

         if(empty($offer_data['tmp_name'])) {
            $upload['error'] = '文件太大';
            return json_encode($upload);
         }
         
         $md5file = md5_file($offer_data['tmp_name']);
         $db = PFactory::getDbo();
         $query = "SELECT id,file_name FROM *PREFIX*calendar_offer_attachment WHERE tag = ".intval($tag)." AND (file_md5 = ".$db->quote($md5file)." OR file_name = ".$db->quote($filename).") AND userid = ".intval($user_id);
         $db->setQuery(PUtil::replaceOCPrefix($db->useMaster($query)));
         $attachment = $db->loadAssoc();
         if ($attachment){
            $upload['error'] = '已存在内容相同的附件, 附件文件名:'.$attachment['file_name'];
            $upload['attchid'] = $attachment['id'];
            return json_encode($upload);
         }
         $creator_name = OC_USER::getDisplayName();
         $query = "INSERT INTO *PREFIX*calendar_offer_attachment (`userid`,`file_name`,`creater`,`file_md5`,`file_path`,`uploadtime`, `tag`)
                   VALUES (".intval($user_id).",".$db->quote($filename).",".$db->quote($creator_name).",".$db->quote($md5file).",".$db->quote($target_path).",UTC_TIMESTAMP,".intval($tag).")
                   ON DUPLICATE KEY UPDATE
                   file_name = values(file_name)";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if (!$db->execute()) {
            $upload['error'] = '网络异常，请刷新页面重试';
            return json_encode($upload);
         }else{
            $upload['attachid'] = $db->insertid();
            $extname = JFile::getExt($offer_data['name']);
            $offer_data['name'] = $upload['attachid'] . "." . $extname;

            $handle = new Upload($offer_data, 'zh_CN');
            if ($handle->uploaded) {
               $handle->allowed = array("application/pdf","application/msword", "application/kswps", 
                                        "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
                                        "application/vnd.ms-office","image/png","image/jpeg","message/rfc822");
               $handle->file_overwrite = true;
               $handle->file_auto_rename = false;
               $handle->file_max_size = 4*1024*1024;
               //for some doc file which have html content
               if(($handle->file_src_mime == 'text/html' || $handle->file_src_mime == 'text/plain') && ($extname == 'doc' || $extname == 'docx' )) {
                  $handle->file_src_mime = 'application/msword';
                  $handle->file_src_name_ext = $extname;
               }
               $handle->process($target_path);
               if (!$handle->processed) {
                  //上传没有成功的话需要删除之前插入的记录,否则数据库有记录但是目录里没文件
                  $query = "DELETE FROM *PREFIX*calendar_offer_attachment WHERE `file_md5` = " . $db->quote($md5file) . " AND userid = " . intval(PFactory::getMasterUser()->id);
                  $db->setQuery(PUtil::replaceOCPrefix($query));
                  $db->execute();
                  
                  $upload['error'] = $handle->error;
                  return json_encode($upload);
               }
               $handle-> clean();
            }
            else {
               $upload['error'] = $handle->error;
               return json_encode($upload);
            }

            $offer_data['name'] = $filename;
            $upload = array_merge($upload, $offer_data);
         }
      }else {
         $upload['error'] = $handle->error;
         return json_encode($upload);
      }

      CacheHelper::triggerRemove($user_id, array('*PREFIX*calendar_offer_attachment'));

      return json_encode($upload);
   }
   
   public function getAttachmentList($type = 'offer'){
      if (!PSession::checkToken('post')) {
         $this->setError('页面已失效，请刷新页面！');
         return false;
      }
      $creator_name = OC_USER::getDisplayName();

      $userid = PFactory::getMasterUser()->id;
      $db = PFactory::getDbo();
      $tag = ($type == 'interview')?2:1;
      $query = "SELECT id,file_name FROM *PREFIX*calendar_offer_attachment WHERE tag = ".intval($tag)." AND userid = ".intval($userid)." ORDER BY id DESC";
      $db->setQuery(PUtil::replaceOCPrefix($db->useMaster($query)));
      return $db->loadAssocList();
   }

   public function deleteAttachment($attachmentid,$type = 'offer'){
      if (empty($attachmentid)) {
         $this->setError("网络异常，请刷新重试！");
         return false;
      }

      // Check owner
      $user = PFactory::getMasterUser();
      $db = PFactory::getDbo();
      $query = "SELECT file_name FROM *PREFIX*calendar_offer_attachment WHERE id =".intval($attachmentid)." AND userid = ".intval($user->id);
      $db->setQuery(PUtil::replaceOCPrefix($db->useMaster($query)));
      $file_name = $db->loadResult();
      if (count($file_name) <= 0) {
         $this->setError("您还没有权限删除此附件");
         return false;
      }

      $datadir = getUserDataDir($user->id);
      $extdir = getUserExtDir($user->id);
      $dir = OC::$SERVERROOT."/data".$datadir."/company/" . $extdir . "/files/".$type."/";
      $filetype = JFile::getExt($file_name);
      if(is_file($dir.$attachmentid.'.'.$filetype)) {
      	if(!unlink($dir.$attachmentid.'.'.$filetype)) {
           $this->setError("删除失败！");
           return false;
      	}
      }
      else {
         // it's already deleted. keep silent 
      }
      // Deletion
      $query = "DELETE FROM *PREFIX*calendar_offer_attachment WHERE id = ".intval($attachmentid)." AND userid = ".intval($user->id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $this->setError("网络异常，请重试！");
         return false;
      }
      
      if($type == 'interview'){
         return $attachmentid;
      }
      return true;
   }

   //获取抄送人
   public function getCopywith(){
      $user = PFactory::getMasterUser();
      $db = PFactory::getDbo();
      $query = "SELECT * FROM *PREFIX*calendar_offer_carboncopy WHERE uid = ".intval($user->id)." GROUP BY copywith ORDER BY copytime DESC limit 0,3";
      $db->setQuery(PUtil::replaceOCPrefix($db->useMaster($query)));
      return $db->loadAssocList();
   }

   public function cancelEvent(){
      if(!PSession::checkToken('post')){
         $this->setError('页面已失效，请刷新页面！');
         return false;
      }

      $eventid = JRequest::getInt('eventid',0);
      $jobid = JRequest::getInt('jobid',0);
      $eventtype = JRequest::getString('eventtype','');
      $jobseekerid = JRequest::getInt('jobseekerid',0);

      if(empty($eventid)||empty($jobid)||empty($eventtype)){
         $this->setError('网络异常，请刷新重试！');
         return false;
      }

      if(!PCompanyHelper::checkJobManagerPermissions($jobid)){
         $this->setError('您没有该操作权限！');
         return false;
      }

      $db = PFactory::getDbo();
      $sql = '';
      $result = 0;
      $where = '';
      if($eventtype == 'VEVENT'){
         $result = PCalendarHelper::INTERVIEW_RESULT_CANCEL;
         $sql = ' result = '.intval($result);
         $statusid = PCalendarHelper::JOBSEEKER_STATUS_CANCEL;
         $where = ' objectid = '.intval($eventid);
      }elseif($eventtype == 'VTODO'){
         $query = "SELECT last_offer_objectid FROM *PREFIX*calendar_interview_result WHERE jobseekerid = ".intval($jobseekerid);
         $db->setQuery(PUtil::replaceOCPrefix($db->useMaster($query)));
         $offereventid = $db->loadResult();

         if(empty($offereventid))
            return false;

         $result = PCalendarHelper::OFFER_FEEDBACKSTATUS_REJECTED;
         $sql = ' offer_stat = '.intval($result);
         $statusid = PCalendarHelper::JOBSEEKER_STATUS_REJECTED;
         $where = ' objectid in ('.intval($eventid).','.intval($offereventid).')';
      }

      $query = "UPDATE *PREFIX*calendar_objects_details SET ".$sql." WHERE ".$where;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $this->setError("网络异常，请重试！");
         return false;
      }
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects_details-'.$eventid));
      CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_objects_details'));

      //send cancel email when event = VEVENT
      if($eventtype == 'VEVENT'){
         if(!self::sendCancelEmail($jobseekerid, $eventid)){
            $this->setError("网络异常，请重试！");
            return false;
         }
      }

      if(!PResumesHelper::setResumeStatus($jobseekerid,$statusid)){
         return false;
      }

      return true;
   }

   public function sendCancelEmail($jobseekerid,$objectid){
      if(empty($jobseekerid) || empty($objectid)){
         return false;
      }

      //check we have send or have not send the cancelemail
      $db = PFactory::getDbo();
      $query = "SELECT canceled,rejected FROM *PREFIX*calendar_interview_result WHERE last_interview_objectid= ".intval($objectid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $cancelemailstatus = $db->loadAssoc();

      if (is_numeric($cancelemailstatus['canceled']) && is_numeric($cancelemailstatus['rejected'])) {
         if ($cancelemailstatus['canceled'] != PCalendarHelper::INTERVIEW_CANCEL) {
            // Update canceled
            $query = "UPDATE *PREFIX*calendar_interview_result SET canceled = ".intval(PCalendarHelper::INTERVIEW_CANCEL)." WHERE jobseekerid=".intval($jobseekerid);
            $db->setQuery(PUtil::replaceOCPrefix($query));
            if (!$db->execute()) {
               return false;
            }
            // Event Driven Cache/Object Caching - clear any cache related to affected tables
            CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_interview_result-'.$objectid));
            CacheHelper::triggerRemove(PFactory::getMasterUser()->get("id"), array('*PREFIX*calendar_interview_result'));

            if ($cancelemailstatus['rejected'] != PCalendarHelper::INTERVIEW_REJECT) {
               $query = "SELECT g.email,g.source, f.comp_name, c.jobseekerid, c.jobseekername, e.source as jobsource, e.job_title, a.startdate, b.is_phone,jus.email AS company_email
                         FROM *PREFIX*calendar_objects a
                         LEFT JOIN *PREFIX*calendar_objects_details b ON b.objectid = a.id
                         LEFT JOIN *PREFIX*calendar_interview_events c ON c.objectid = a.id
                         LEFT JOIN #__jobs_job e ON e.id = b.jobid
                         LEFT JOIN #__jobs_employer f ON f.user_id = e.employer_id
                         LEFT JOIN #__users jus ON jus.id=f.user_id
                         LEFT JOIN *PREFIX*hr_jobseeker g ON g.id = c.jobseekerid
                         WHERE a.id = ".intval($objectid);
               $db->setQuery(PUtil::replaceOCPrefix($query));
               $jobseeker = $db->loadAssoc();
               PCalendarHelper::sendJobseekerCancelEmail($jobseeker);

               $interviewers = PCalendarHelper::getInterviewerEmailDataFromParentid($objectid);
               foreach ($interviewers as $k => $user) {
                  PCalendarHelper::sendInterviewCancelEmail($user);
               }
            }
            
            // Update jobseeker's status in oc_hr_jobseeker table
            if (!PResumesHelper::setResumeStatus($jobseekerid,PCalendarHelper::JOBSEEKER_STATUS_CANCEL)) {
               return false;
            }
         }
      }
      return true;
   }
}
